<?php

/**
 * Description of SeoParserModel
 *
 * @author NE
 */
class RCMS_Object_Parser_SeoParserModel extends RCMS_Core_BaseModel {

    protected $_deeplinkTableName = 'deeplink';
    protected $_contentTableName = 'content';
    protected $_deeplinkAndContentTableName = 'deeplink_content';
    
	public function checkExists($id, $link) {
		$select = $this->_adapter->select()
			->from(
				array('lf' => 'link_reference'),
				array(
					'containerId' => 'id_container',
					'link'
				)
			)
			->where('id_container=?', $id)
			->where('link=?', $link);
		return $this->_adapter->fetchAll($select, null, Zend_Db::FETCH_ASSOC);
	}

	public function getSitePagesUrls() {
		$select = $this->getAdapter()->select()
			->from(
				array('p' => 'page'),
				array(
					'p.url'
				)
			);
		return $this->getAdapter()->fetchCol($select);
	}

	/**
	 * Get all pages
	 *
     * @return array
     */
    public function selectAllPages()
    {
        $select = $this->_adapter->select()
			->from(
				array('p' => 'page'),
				array(
					'url',
					'categoryId' => 'category_id'
				)
			)
			->joinLeft(
				array('c' => 'category'),
				'c.id = p.category_id',
				array(
					'parentCategoryId' => 'id_parent',
				)
			)
			->where('url <> "no-category" ')
			->where('url <> "?"', RCMS_Object_QuickConfig_QuickConfig::$draftCategoryUrl)
			->where('is_404page != \'1\'');
        return $this->_adapter->fetchAll($select);
    }

	public function insertLinkReference($containerId, $link) {
		$referenceData = array(
			'id_container' => $containerId,
			'link' => $link
		);
		$result = $this->_adapter->insert('link_reference', $referenceData);
	}

	public function getLinkRefContent() {
		$select = $this->getAdapter()->select()
			->from(
				array('lf' => 'link_reference'),
				array(
					'containerId' => 'id_container',
					'link'
				)
			);
		return $this->getAdapter()->fetchAll($select, null, Zend_Db::FETCH_ASSOC);
	}

	public function getContainersIdsForSync($url, $domain) {
		$select = $this->getAdapter()->select()
			->from(
				array('lf' => 'link_reference'),
				array(
					'containerId' => 'id_container',
				)
			)
			->where('link=?', $url)
			->orWhere('link=?', $domain . $url);
		return $this->getAdapter()->fetchCol($select, null, Zend_Db::FETCH_ASSOC);
	}

	public function updateLinkReference($containerId, $oldLink, $newLink, $domain) {
		$updData = array(
			'link' => $domain .$newLink
		);
		$updateWhere = sprintf('id_container=%d AND (link=\'%s\' OR link=\'%s\')', $containerId, $oldLink, $domain.$oldLink);
		return $this->_adapter->update('link_reference', $updData, $updateWhere);
	}

	public function insertRenamedPage($id, $url, $newUrl) {
		$url = ($this->_getExtension($url) == '.html') ? $url : $url . '.html';
		$newUrl = str_replace('.html', '', $newUrl);
		$data = array(
			'id_page' => $id,
			'url' => $url,
			'new_url' => urldecode($newUrl)
		);
		return $this->_adapter->insert('renamed_pages', $data);
	}

	private function _getExtension($url) {
		$pu = parse_url($url);
		return substr($pu['path'], (strlen($pu['path'])-5));
	}

	public function getRenamedPageById($id) {
		$select = $this->getAdapter()->select()->distinct()
			->from(
				array('rp' => 'renamed_pages'),
				array(
					'oldUrl' => 'url',
					'pageId' => 'id_page',
					'currUrl' => 'new_url'
				)
			)
			->where('id_page=?', $id);
		return $this->_adapter->fetchAll($select, null, Zend_Db::FETCH_ASSOC);
	}

	public function getRenamedPages() {
		$select = $this->getAdapter()->select()->distinct()
			->from(
				array('rp' => 'renamed_pages'),
				array(
					'oldUrl' => 'url',
					'pageId' => 'id_page',
					'currUrl' => 'new_url'
				)
			);
		return $this->_adapter->fetchAll($select, null, Zend_Db::FETCH_ASSOC);
	}

    public function selectAllDeeplinks()
    {
        $select = $this->_adapter->select()->from($this->_deeplinkTableName)->where("ban = '0' AND name != '' AND url != ''");
        return $this->_adapter->fetchAll($select);
    }

    public function selectContents($containerId = 0)
    {
		if(!$containerId) {
			$sql = $this->_adapter->select()
				->from(
					array('page' => 'page'),
					array(
						  'page_name' => 'nav_name',
						  'page_url' => 'url',
						  'page_h1' => 'h1'
						  )
					)
				->joinLeft(
					array('container' => 'container'),
							'container.page_id = page.id',
							array('container_id' => 'id')
					)
				->joinLeft(
					array('content' => 'content'),
						'content.id_container = container.id',
					array(
							'content_id' => 'id',
							'content_value' => 'value'
							)

					)
				 ->where("container.container_type_id!=3");
		}
		else {
			$sql = $this->_adapter->select()
				->from(
					array('page' => 'page'),
					array(
						  'page_name' => 'nav_name',
						  'page_url' => 'url',
						  'page_h1' => 'h1'
						  )
					)
				->joinLeft(
					array('container' => 'container'),
							'container.page_id = page.id',
							array('container_id' => 'id')
					)
				->joinLeft(
					array('content' => 'content'),
						'content.id_container = container.id',
					array(
							'content_id' => 'id',
							'content_value' => 'value'
							)

					)
				 ->where("container.container_type_id!=3")
				 ->where("container.id = ?", $containerId);
		}
           
          return $this->_adapter->fetchAll($sql);
    }

    public function updateContent($id, $content)
    {
        $data = array(
            'value' => $content
        );
        $where = sprintf("id = %d", $id);
        return $this->_adapter->update($this->_contentTableName, $data, $where);
    }

	public function replaceDeeplinksUrl($oldUrl, $newUrl) {
		//CALL replaceDeeplinkUrl('http://rcms.dev/index1234.html', 'http://rcms.dev/index.html')
		$statement = $this->getAdapter()->query("CALL replaceDeeplinkUrl('$oldUrl', '$newUrl')");
	}

    public function insertIdDeeplinkAndContent($deeplinkId, $contentId)
    {
        $query = "
			INSERT INTO
				`deeplink_content`(`deeplink_id`, `content_id`)
			VALUES
				(?, ?)
			ON DUPLICATE KEY UPDATE
				`deeplink_id` = ?,
				`content_id`  = ?;
		";
		$data = array(
			'deeplink_id' => $deeplinkId,
			'content_id' => $contentId
		);
        
		return $this->getAdapter()->query($query, array_merge(array_values($data), array_values($data)));
		//return $this->_adapter->insert($this->_deeplinkAndContentTableName, $data);
    }

    public function selectDeeplinkAndContentById($id)
    {
        $sql = $this->_adapter->select()
        ->distinct()
        ->from(
            array('dc' => $this->_deeplinkAndContentTableName),
            array(
                  'deeplink_id' => 'deeplink_id',
                  'content_id' => 'content_id'
                  )
        )
        ->join(
            array('c'=> $this->_contentTableName),
                'c.id = dc.content_id',
            array('content' => 'value')
        )
        ->join(
            array('d' => $this->_deeplinkTableName),
            'd.id = dc.deeplink_id',
            array(
                'name_deeplink' => 'name',
                'url_deeplink'  => 'url'
                )
        )
        ->where('deeplink_id = ?', $id);
        return $this->_adapter->fetchAll($sql);
    }

    public function deleteDeeplinkAndContentByDeeplinkId($id)
    {       
        $where = sprintf("deeplink_id = %d", $id);
        return $this->_adapter->delete($this->_deeplinkAndContentTableName, $where);
    }

	/**
	 * Find incorrect record in redorect table
	 *
	 * @param url $url
	 * @return int
	 */
	public function selectBadRedirectId($url) {
		$select = $this->getAdapter()->select()
			->from(
				array('rp' => 'renamed_pages'),
				array(
					'id_page'
				)
			)
			->where('url=?', $url);
			return $this->getAdapter()->fetchOne($select);
	}

	/**
	 * Clean redirect table from incorrect record
	 *
	 * @param integer $id incorrect record id
	 * @param string $url old redirect url
	 * @return boolean
	 */
	public function cleanBadRedirect($id, $url) {
		$deleteCondition = sprintf("id_page=%d AND url='%s'", $id, $url);
		$result = $this->getAdapter()->delete('renamed_pages', $deleteCondition);
		if(!$result) {
			$deleteCondition = sprintf("id_page=%d AND url='%s'", $id, addslashes($url));
			$result = $this->getAdapter()->delete('renamed_pages', $deleteCondition);
			return $result;
		}
	}

	public function selectPageIdByUrl($url) {
		$select = $this->getAdapter()->select()
			->from(
				array('p' => 'page'),
				array(
					'id'
				)
			)
			->where('p.url=?', $url);
		return $this->getAdapter()->fetchOne($select);
	}

	public function getPagesUrlsAndIds() {
	   $select = $this->getAdapter()->select()
	   ->from(
		   array('p' => 'page'),
		   array(
			   'id',
			   'url'
		   )
	   );
	   return $this->getAdapter()->fetchAll($select, null, Zend_Db::FETCH_ASSOC);
   }

   public function selectDraftCategoryId() {
	   $select = $this->getAdapter()->select()
		->from(
			array('c' => 'category'),
			array(
				'id'
			)
		)
		->where('c.name=?', RCMS_Object_QuickConfig_QuickConfig::$draftCategoryName);
		return $this->getAdapter()->fetchOne($select);
   }
}
